# --coding:utf-8--
from object import Connect


#  网关信息入库
class GW_arehousing(Connect):

    #  启动数据库连接
    def __init__(self):
        super(GW_arehousing, self).__init__(db="basics_db")  # 可选择要连接的数据库

    #  断开数据库连接
    def _mysql_close(self):
        super(GW_arehousing, self).mysql_close()

    # 查看设备网关信息（`basics_db`.`gw_status_info`）
    def gw_look_sql(self, serial_number_id):
        sql = "SELECT * FROM `basics_db`.`gw_status_info` WHERE serial_number_id = '%s'" % serial_number_id
        return sql

    #  将设备信息写入到"basics.db"表下面的"gw_status_info"中
    def gw_status_info_sql(self, serial_number_id, device_type, online_time, status, gw_Location, belong, PK, DN,
                           hard_version, soft_version, gw_context):
        sql = "INSERT INTO `basics_db`.`gw_status_info` (`serial_number_id`, `device_type`,`online_time`,`status`,`gw_Location`,`belong`,`PK`,`DN`,`hard_version`,`soft_version`,`gw_context`) VALUES ('%s', '%s','%s','%s','%s','%s','%s','%s','%s','%s','%s') ; " % (
            serial_number_id, device_type, online_time, status, gw_Location, belong, PK, DN,
            hard_version, soft_version, gw_context)
        return sql

    #  将设备信息写入"basics_db"表下面的"equipment_production_info"中
    def equipment_sql(self, serial_number_id, device_model, manufacturer, production_batch,
                      production_data,
                      device_type):
        sql = "INSERT INTO `basics_db`.`equipment_production_info`(`serial_number_id`,`device_model`,`manufacturer`,`production_batch`,`production_data`,`device_type`) VALUES ('%s','%s','%s','%s','%s','%s');" % (
            serial_number_id, device_model, manufacturer, production_batch, production_data, device_type)
        return sql

    #  删除设备网关信息（`basics_db`.`gw_status_info`）
    def gw_delete_sql(self, serial_number_id):
        sql = "DELETE FROM `basics_db`.`gw_status_info` WHERE serial_number_id = '%s'" % serial_number_id
        return sql

    def query_data(self, sql):
        print(sql)
        self.cursor.execute(sql)  # 执行sql语句
        try:
            results = self.cursor.fetchall()
            print(results)  # 打印返回结果
        except:
            print("Error: unable to fetch data")
            return "error"
        self.conn.commit()  # 事务提交
        return results


#  微断信息入库
class MCB_arehousing(Connect):
    def __init__(self):
        super(MCB_arehousing, self).__init__()

    def _mysql_close(self):
        super(MCB_arehousing, self).mysql_close()

    # 查看设备微断信息（`basics_db`.`mcb_status_info`）
    def mcb_look_sql(self, serial_number_id):
        # sql = "SELECT * FROM `basics_db`.`mcb_status_info` WHERE serial_number_id = '%s'" % serial_number_id
        sql = "SELECT * FROM `equipment_production_info` LEFT JOIN `gw_status_info` ON `equipment_production_info` .`serial_number_id`= `gw_status_info`.`serial_number_id` WHERE `equipment_production_info`.`serial_number_id` = '%s';" % serial_number_id
        # sql = "SELECT COUNT(*) FROM `equipment_production_info` LEFT JOIN `gw_status_info` ON `equipment_production_info` .`serial_number_id`= `gw_status_info`.`serial_number_id` WHERE `equipment_production_info`.`serial_number_id` = 'A02104011047';"
        return sql

    #  将设备微断信息写入到"basics.db"表下面的"mcb_status_info"中
    def mcb_status_info_sql(self, serial_number_id, device_type, online_time, status, belong, address, hard_version,
                            soft_version, Location, mcb_context):
        sql = "INSERT INTO `basics_db`.`mcb_status_info`( `serial_number_id` , `device_type` , `online_time` , `status` , `belong` , `address` , `hard_version` , `soft_version` , `Location` , `mcb_context`) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s') ;" % (
            serial_number_id, device_type, online_time, status, belong, address, hard_version, soft_version,
            Location, mcb_context)
        return sql

    #  将设备微断信息写入"basics_db"表下面的"equipment_production_info"中
    def equipment_sql(self, serial_number_id, device_model, manufacturer, production_batch,
                      production_data,
                      device_type):
        sql = "INSERT INTO `basics_db`.`equipment_production_info`(`serial_number_id`,`device_model`,`manufacturer`,`production_batch`,`production_data`,`device_type`) VALUES ('%s','%s','%s','%s','%s','%s');" % (
            serial_number_id, device_model, manufacturer, production_batch, production_data, device_type)
        return sql

    #  将设备微断信息添加到"rtdb"表下面的"analog_tab"和"digiting_tab"中
    def mcb_rtdb_sql(self, uuid):
        sql = "INSERT INTO `rtdb`.`analog_tab` (`uuid`) VALUES ('%s')" % uuid
        return sql

    #  删除设备信息（`basics_db`.`mcb_status_info`）
    def mcb_delete_sql(self, serial_number_id):
        sql = "DELETE FROM `basics_db`.`mcb_status_info` WHERE serial_number_id = '%s'" % serial_number_id
        return sql

    def query_data(self, sql):
        print(sql)
        self.cursor.execute(sql)  # 执行sql语句
        try:
            results = self.cursor.fetchall()
            print(results)  # 打印返回结果
        except:
            print("Error: unable to fetch data")
            return "error"
        self.conn.commit()  # 事务提交
        return results


if __name__ == "__main__":
    #  网关
    gw = GW_arehousing()

    result = gw.gw_look_sql("cs123")
    gw.query_data(result)
    # result1 = gw.gw_status_info_sql("abc", "-", "-", "-", "-", "-", "-", "-", "-", "-", "-")
    # gw.query_data(result1)
    # result2 = gw.equipment_sql("abc", "-", "-", "-", "-", "-")
    # gw.query_data(result2)
    # result3 = gw.gw_delete_sql("abc")
    # gw.query_data(result3)
    gw._mysql_close()  # 断开连接

    # 微断
    # mcb = MCB_arehousing()

    # result = mcb.mcb_look_sql("202104011074")
    # mcb.query_data(result)
    # result1 = mcb.mcb_status_info_sql("aaa", "-", "-", "-", "-", "-", "-", "-", "-", "-")
    # mcb.query_data(result1)
    # result2 = mcb.equipment_sql("aaa", "-", "-", "-", "-", "-")
    # mcb.query_data(result2)
    # result3 = mcb.mcb_rtdb_sql("123321")
    # mcb.query_data(result3)
    # result4 = mcb.mcb_delete_sql("aaa")
    # mcb.query_data(result4)
    # mcb._mysql_close()  # 断开连接
